package cn.ichiva.luckysheet.jdbc;

import java.sql.*;
import java.util.*;

/**
 * TODO
 *
 * @author pzq
 * @version 1.0
 * @date 2021/11/30 17:11
 */
public class jdbcUtil {
    public static List<Map<String, Object>> jdbcUtils(String sql) throws ClassNotFoundException, SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        //1.加载驱动
        Class.forName("org.postgresql.Driver");
        //2.设置连接数据库参数
        String url = "jdbc:postgresql://8.140.171.1:1921/xkeps";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "123456");
        //3.连接数据库
        Connection conn = DriverManager.getConnection(url, props);
        //4.编写查询语句，创建statement
//        String sql = "select * from sys_user where user_id = 141";
        PreparedStatement ps = conn.prepareStatement(sql);
//        ps.setObject(1, "141");
        //5.查询数据库
        ResultSet rs = ps.executeQuery();
        //6.操作结果集
        list=toListMap(rs);
        //7.关闭连接
        if(ps != null) {
            ps.close();
        }
        if(rs != null) {
            rs.close();
        }
        if(conn != null) {
            conn.close();
        }
        return list;
    }

    public static List<Map<String, Object>> toListMap(ResultSet ret) throws SQLException{
        List<Map<String, Object>> list = new ArrayList();
        ResultSetMetaData meta = ret.getMetaData();
        int cot = meta.getColumnCount();

        while(ret.next()) {
            Map<String, Object> map = new HashMap();
            for(int i = 0; i < cot; i++) {
                map.put(meta.getColumnName(i + 1), ret.getObject(i + 1));
            }
            list.add(map);
        }

        return list;
    }
}
